<?php
include_once("../Dao/db.php");
class InsertDB
{
	var $memberId ;
	
	var $campaing_id;
	
	var $adgroup_id=array();
	
	var $keyword_id=array();
	
	var $creative_id=array();
	
	function run()
	{
		$this->getAdgroupID();
		//$this->getKeyWordID();
		//$this->getCreativeID();
		//$this->insertData();
		//$this->testInsert();
		//$this->insertCVData();
		//$this->insertEngineData();
		//$this->insertQueryData();
		//$this->insertKeyWordData();
		//$this->insertCreativeData();
	}
	
	function getMemberID()
	{
		$sql = "select memberId  from member ";
		$result = mysql_query($sql);

		$rows = mysql_num_rows($result);
		if ($rows > 0)
		{
			for ($i=0;$i<$rows;$i++)
			{
				$data = mysql_fetch_row($result);				
				$this->memberId[i] =  $data[0];			
			}			
		}
	} 
	
	function getAdgroupID()
	{/*
		$sql = " select campaign.memberId,campaign.camp_id, adgroup.adGr_Id";
		$sql .= " FROM campaign, adgroup ";
		$sql .= " where campaign.camp_Id = adgroup.camp_Id";
		//echo $sql;
		$result = mysql_query($sql);

		while ($row = mysql_fetch_array($result)) {
			array_push($this->adgroup_id,$row);
		}*/
		$query = "SELECT * FROM adgroup WHERE ";
                //GoogleAdwordsのレポート
                $query = "SELECT * FROM creative WHERE listingType=1 AND memberId=81 AND creativeId=2812704454 AND status != 9";	
                $result = mysql_query($query);
                $row = mysql_fetch_array($result);
                //Googleからの広告グループはローカルデータベースに存在しない場合、エラーになる
                if(!$row)
                {
                    echo $query;
                    echo "広告グループID[$adGroupId]はローカルデータベースに管理しないかもしれません\r\n";
                    //continue;
                }
	}
	
	function getKeyWordID()
	{
		$sql = " select campaign.memberId,campaign.camp_id, adgroup.adGr_Id,keyword.key_id";
		$sql .= " FROM campaign, adgroup,keyword ";
		$sql .= " where campaign.camp_Id = adgroup.camp_Id";
		$sql .= " AND keyword.adGr_Id = adgroup.adGr_Id ";
		//echo $sql;
		$result = mysql_query($sql);

		while ($row = mysql_fetch_array($result)) {
			array_push($this->keyword_id,$row);
		}
	}
	
	function getCreativeID()
	{
		$sql = " select campaign.memberId,campaign.camp_id, adgroup.adGr_Id,creative.Crt_Id";
		$sql .= " FROM campaign, adgroup,creative ";
		$sql .= " where campaign.camp_Id = adgroup.camp_Id";
		$sql .= " AND creative.adGr_Id = adgroup.adGr_Id ";
		//echo $sql;
		$result = mysql_query($sql);

		while ($row = mysql_fetch_array($result)) {
			array_push($this->creative_id,$row);
		}
	}
	
	function insertData()
	{
		//echo date("Y-m-d");
		$today = date("Y-m-d");
		$week = date("w");
		$monday = date("Y-m-d",mktime(0, 0, 0, date("m") , date("d")-$week));
		foreach($this->adgroup_id as $group){
			//echo $group[0]." ".$group[1]." ".$group[2]."<br>";
			$sql = " INSERT INTO adreport (date,memberId,listingType,campaign_Id,adGroup_Id, ";
			$sql .= "weekly,dayOfTheWeek,status,impression,clickCnt,cost) ";
			$sql .= " VALUES ( ";
			$sql .= "'".$today."',".$group[0].",1, ".$group[1].", ".$group[2].",YEARWEEK('".$today."'), ".$week.",".rand(1,3).",".rand(1,3456).",".rand(0,1234).",".rand(0,500).")";
			//echo $sql."<br>";
			$result = mysql_query($sql);
			if (mysql_affected_rows() < 1){
				echo "adreportErrorは発生した!<br>";
			}else{
				echo " 【CAMPID】:".$group[1]." 【GROUPID】：".$group[2]." adreport成功！<br>";
			}	
		}
		
	}
	
	function insertCVData()
	{
				//echo date("Y-m-d");
		$today = date("Y-m-d");
		$week = date("w");
		$monday = date("Y-m-d",mktime(0, 0, 0, date("m") , date("d")-$week));
		foreach($this->adgroup_id as $group){
			//echo $group[0]." ".$group[1]." ".$group[2]."<br>";
			$sql = " INSERT INTO listing.adcvreport (date ,memberId ,listingType ,campaign_Id ,adGroup_Id ,";
			$sql .= " cvId ,status ,cv ,sales ,salesValue ,clickCvSpan ,postCvCnt ,directCvCnt ,indirectCvCnt ,asistCvCnt ) ";
			$sql .= " VALUES (";
			$sql .= "'".date("Y-m-d")."',".$group[0].",1, ".$group[1].", ".$group[2];
			$sql .= ",1,1,".rand(0,400).",".rand(0,400).",".rand(0,400).",".rand(0,400).",".rand(0,400).",".rand(0,400).",".rand(0,400).",".rand(0,400).")";
			echo $sql."<br>";
			
			$result = mysql_query($sql);
			if (mysql_affected_rows() < 0){
				echo "adcvreportErrorは発生した!<br>";
			}else{
				echo " 【CAMPID】:".$group[1]." 【GROUPID】：".$group[2]." Total:".mysql_affected_rows()." adcvreport成功！<br>";
			}	
		}
		
	}
	
	function insertEngineData()
	{
		$today = date("Y-m-d");
		$week = date("w");
		$monday = date("Y-m-d",mktime(0, 0, 0, date("m") , date("d")-$week));
		foreach($this->keyword_id as $keyword){
			//echo $group[0]." ".$group[1]." ".$group[2]."<br>";
			$sql = ' INSERT INTO searchenginereport (date, campaign_id, adgroup_id,';
			$sql .= ' MemberID, listingType, keyword_id, clickCnt, SearchEngine_Id, weekly, dayOfTheWeek)';
			$sql .= ' VALUES (\''.$today.'\','.$keyword[1].','.$keyword[2].',';
			$sql .= $keyword[0].',1, '.$keyword[3].','.rand(0,400).','.rand(1,15).',';
			$sql .= 'YEARWEEK(\''.$today.'\'),'.$week.')';	
			$result = mysql_query($sql);
			if (mysql_affected_rows() < 1){
				echo "Engineは発生した!<br>";
			}else{
				echo " 【CAMPID】:".$keyword[1]." 【GROUPID】：".$keyword[2]." Engine成功！<br>";
			}
		}
	}
	
	function insertQueryData()
	{
		$today = date("Y-m-d");
		$week = date("w");
		$monday = date("Y-m-d",mktime(0, 0, 0, date("m") , date("d")-$week));
		foreach($this->keyword_id as $keyword){
			//echo $group[0]." ".$group[1]." ".$group[2]."<br>";
			$sql = ' INSERT INTO queryreport (date, campaign_id, adgroup_id,';
			$sql .= ' MemberID, listingType, keyword_id, clickCnt, query, weekly, dayOfTheWeek)';
			$sql .= ' VALUES (\''.$today.'\','.$keyword[1].','.$keyword[2].',';
			$sql .= $keyword[0].',1, '.$keyword[3].','.rand(0,400).',CONCAT(\'QUERY\','.rand(1,20).'),';
			$sql .= 'YEARWEEK(\''.$today.'\'),'.$week.')';	
			
		//	echo $sql."<br>";
			

			$result = mysql_query($sql);
			if (mysql_affected_rows() < 1){
				echo "QueryErrorは発生した!<br>";
			}else{
				echo " 【CAMPID】:".$keyword[1]." 【GROUPID】：".$keyword[2]." Query成功！<br>";
			}
		}
	}
	
	function insertKeyWordData()
	{
		$today = date("Y-m-d");
		$week = date("w");
		$monday = date("Y-m-d",mktime(0, 0, 0, date("m") , date("d")-$week));
		foreach($this->keyword_id as $keyword){
			echo "KEYWORD:".$keyword[3];
			$sql = 'INSERT INTO `listing`.`keywordsreport` (`date`, `memberId`, `listingType`, `campaign_Id`, `adGroup_Id`, `keyword_Id`, `weekly`, `dayOfTheWeek`, `status`, `impression`, `clickCnt`, `cost`, `position`) ';
			$sql.= 'VALUES (\''.$today.'\','.$keyword[0].',1,'.$keyword[1].','.$keyword[2].','.$keyword[3].',YEARWEEK(\''.$today.'\'),'.$week.',1, '.rand(0,400).','.rand(0,400).','.rand(0,400).','.rand(1,20).');';
	
			
		//echo $sql."<br>";
			
			$result = mysql_query($sql);
			if (mysql_affected_rows() < 0){
				echo "KeywordErrorは発生した!<br>";
			}else{
				echo " 【CAMPID】:".$keyword[2]." 【GROUPID】：".$keyword[3]." Keyword成功！<br>";
			}
			
			$sql = "
				INSERT into cvtracking
				(MemberID,cvId,clickDate,cvDate,uid,ip,
				camp_id,adgr_id,crt_id,key_id,clickCvSpan,postCvFlag,
				directCvFlag,indirectCvFlag,asistCvFlag,listingType,sales
				)
				values
				(".$keyword[0].",".rand(1,8).",now(),now(),'Ezweb.co.jp".rand(10000,50000)."','172.168.3.214',"
				.$keyword[1].",".$keyword[2].",null,".$keyword[3].",".rand(1,30).",".rand(0,1).","
				.rand(0,1).",".rand(0,1).",".rand(0,1).",1,".rand(300,200000)."
				) ;";
				//echo $sql;
			mysql_query($sql);
			
			
		}
	}
	
	function insertCreativeData()
	{
		$today = date("Y-m-d");
		$week = date("w");
		$monday = date("Y-m-d",mktime(0, 0, 0, date("m") , date("d")-$week));
		foreach($this->creative_id as $creative){
			//echo $group[0]." ".$group[1]." ".$group[2]."<br>";
			$sql = 'INSERT INTO `listing`.`creativereport` (`date`, `memberId`, `listingType`, `campaign_Id`, `adGroup_Id`, `creative_Id`, `weekly`, `dayOfTheWeek`, `status`, `impression`, `clickCnt`, `cost`, `position`) ';
			$sql.= 'VALUES (\''.$today.'\','.$creative[0].',1,'.$creative[1].','.$creative[2].','.$creative[3].',YEARWEEK(\''.$today.'\'),'.$week.',1, '.rand(0,400).','.rand(0,400).','.rand(0,400).','.rand(1,20).');';
	
			
		//echo $sql."<br>";
			
			$result = mysql_query($sql);
			if (mysql_affected_rows() < 1){
				echo "CreativeErrorは発生した!<br>";
			}else{
				echo " 【CAMPID】:".$group[1]." 【GROUPID】：".$group[2]." Creative成功！<br>";
			}
			
			
			
			$sql = "
				INSERT into cvtracking
				(MemberID,cvId,clickDate,cvDate,uid,ip,
				camp_id,adgr_id,crt_id,key_id,clickCvSpan,postCvFlag,
				directCvFlag,indirectCvFlag,asistCvFlag,listingType,sales
				)
				values
				(".$creative[0].",".rand(1,8).",now(),now(),'Ezweb.co.jp".rand(10000,50000)."','172.168.3.214',"
				.$creative[1].",".$creative[2].",".$creative[3].",null,".rand(1,30).",".rand(0,1).","
				.rand(0,1).",".rand(0,1).",".rand(0,1).",1,".rand(300,200000)."
				) ;";
				//echo $sql;
			mysql_query($sql);
		}
	}
	
	function testInsert()
	{
		$sql = 'INSERT INTO `listing`.`BatchT` (`ID`, `Name`, `DateTime`) VALUES (NULL, \'Sample\', NOW());'; 
		$result = mysql_query($sql);
		if (mysql_affected_rows() < 1){
			echo "Errorは発生した！<br>";
		}	
	}
}

$instance = new InsertDB;
$instance->run();
?>